package cn.edu.sdjzu.xg.dao;

import cn.edu.sdjzu.xg.domain.University;
import cn.edu.sdjzu.xg.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

public class UniversityDao {
    private static UniversityDao universityDao= new UniversityDao();
    private UniversityDao(){}
    public static UniversityDao getInstance(){
        return universityDao;

    }
    public int add(University university)throws SQLException {
        Connection connection= JdbcHelper.getConn();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into university " + "(description,no)" +
                "values(?,?)");
        preparedStatement.setString(1,university.getDescription());
        preparedStatement.setString(2,university.getNo());
        int affectRow = preparedStatement.executeUpdate();
        return affectRow;

    }
    public University find(Integer id)throws  SQLException{
        University desiredUniversity= null;
        Connection connection= JdbcHelper.getConn();
        String sql_select_id = "select * from university where id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if( resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks=resultSet_select_id.getString("remarks");
            desiredUniversity=new University(id,description,no,remarks);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredUniversity;


    }
    public boolean update(University university)throws SQLException{
        Connection connection=JdbcHelper.getConn();
        String sql_update = "update university set description=?,no=? ,remarks=?"
                +"where id = ?";
        PreparedStatement pstmt_update=connection.prepareStatement(sql_update);
        pstmt_update.setString(1,university.getDescription());
        pstmt_update.setString(2,university.getNo());
        pstmt_update.setInt(3,university.getId());
        pstmt_update.setString(4,university.getRemarks());
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update,connection);
        return rowAffected>0;

    }
    public boolean delete(Integer id)throws SQLException{
        Connection connection = JdbcHelper.getConn();
        String sql_delete = "delete from university where id = ?";
        PreparedStatement pstmt_delete = connection.prepareStatement(sql_delete);
        pstmt_delete.setInt(1, id);
        int rowAffected = pstmt_delete.executeUpdate();
        JdbcHelper.close(pstmt_delete,connection );
        return rowAffected>0;


    }
    public Collection<University> findAll() throws SQLException {
        Collection<University> desiredUniversities = new TreeSet<University>();
        Connection connection = JdbcHelper.getConn();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from University");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks=resultSet.getString("remarks");
            University desiredUniversity = new University(id, description, no,remarks);
            desiredUniversities.add(desiredUniversity);
        }
        JdbcHelper.close(resultSet,preparedStatement,connection);
        return desiredUniversities;
    }
    public int count(Connection connection)throws SQLException{
        int countNum = 0;
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) as cnt_University FROM University ");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            countNum = resultSet.getInt("cnt_University");
        }
        return countNum;
    }
}
